package top.jacktgq.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import top.jacktgq.pojo.PageBean;
import top.jacktgq.pojo.Student;
import top.jacktgq.utils.HikariUtils;

import javax.sql.DataSource;
import javax.sql.rowset.serial.SerialBlob;
import java.sql.*;
import java.util.ArrayList;

/**
 * @Author CandyWall
 * @Date 2021/6/20--18:57
 * @Description
 */
public class StudentDao {
    public void addStudent(Student student) throws SQLException {
        QueryRunner runner = new QueryRunner(HikariUtils.getDataSource());
        String sql = "INSERT INTO `studentsystem`.`student` (`stu_id`, `name`, `email`, `birthdate`, `age`, `sex`, `phone`, `photo_byte`, `photo_base64`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        runner.update(sql, student.getStu_id(), student.getName(), student.getEmail(), student.getBirthdate(), student.getAge(), student.getSex(), student.getPhone(), new SerialBlob(student.getPhoto_byte()), student.getPhoto_base64());
    }

    public PageBean<Student> getStudents(int currentPage, int currentCount) throws SQLException {
        QueryRunner runner = new QueryRunner(HikariUtils.getDataSource());

        //新建分页对象
        PageBean<Student> pageBean = new PageBean<Student>();
        //1、封装当前页的页号
        pageBean.setCurrentPage(currentPage);

        //2、封装当前页显示的条数
        pageBean.setCurrentCount(currentCount);

        //3、封装显示的总条数
        Long totalCount = runner.query("SELECT COUNT(*) FROM student", new ScalarHandler<>());
        pageBean.setTotalCount(totalCount.intValue());

        //4、封装显示的总页数
        int totalPage = (int) Math.ceil(1.0 * totalCount / currentCount);
        pageBean.setTotalPage(totalPage);

        //5、封装当前页的信息
        //计算当前页第一条数据的索引
        int index = (currentPage - 1) * currentCount;
        String sql = "SELECT student.stu_id,student.`name`,student.email,student.birthdate,student.age,student.sex,student.phone FROM student LIMIT ?,?";
        pageBean.setList(runner.query(sql, new BeanListHandler<Student>(Student.class), index, currentCount));
        return pageBean;
    }

    public byte[] getPhoto(String stu_id) throws SQLException {
        Connection conn = HikariUtils.getDataSource().getConnection();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT student.photo_byte FROM student WHERE stu_id=?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, stu_id);
            rs = pstm.executeQuery();
            if(rs.next()) {
                Blob blob = rs.getBlob(1);
                if (blob != null) {
                    return blob.getBytes(1, Math.toIntExact(blob.length()));
                }
            }

            return null;
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstm != null) {
                pstm.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

    // 根据学号删除学生信息
    public void delStudent(String stu_id) {
        QueryRunner runner = new QueryRunner(HikariUtils.getDataSource());
        try {
            runner.update("delete from student where stu_id=?", stu_id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据学号获取学生信息
    public Student getStudentById(String stu_id) throws SQLException {
        QueryRunner runner = new QueryRunner(HikariUtils.getDataSource());
            return runner.query("SELECT student.stu_id,student.`name`,student.email,student.birthdate,student.age,student.sex,student.phone FROM student WHERE stu_id=?", new BeanHandler<Student>(Student.class), stu_id);
    }

    public void editStudent(Student student) throws SQLException {
        QueryRunner runner = new QueryRunner(HikariUtils.getDataSource());
        String sql = "UPDATE `student` SET `name`=?, `email`=?, `birthdate`=?, `age`=?, `sex`=?, `phone`=? WHERE stu_id=?";
        runner.update(sql, student.getName(), student.getEmail(), student.getBirthdate(), student.getAge(), student.getSex(), student.getPhone(), student.getStu_id());
    }


    public PageBean<Student> getStudentsByCondition(int currentPage, int currentCount, String name, String sex) throws SQLException {
        QueryRunner runner = new QueryRunner(HikariUtils.getDataSource());

        //新建分页对象
        PageBean<Student> pageBean = new PageBean<Student>();
        //1、封装当前页的页号
        pageBean.setCurrentPage(currentPage);

        //2、封装当前页显示的条数
        pageBean.setCurrentCount(currentCount);

        //3、封装显示的总条数
        String sql1 = "SELECT COUNT(*) FROM student WHERE 1=1";
        ArrayList<Object> params = new ArrayList<>();
        if (name != null && !name.equals("")) {
            sql1 += " AND `name` like ?";
            params.add("%" + name + "%");
        }
        if (sex != null && !sex.equals("")) {
            sql1 += " AND sex=?";
            params.add(sex);
        }
        // System.out.println("sql1" + sql1);
        Long totalCount = runner.query(sql1, new ScalarHandler<>(), params.toArray(new Object[0]));
        pageBean.setTotalCount(totalCount.intValue());

        //4、封装显示的总页数
        int totalPage = (int) Math.ceil(1.0 * totalCount / currentCount);
        pageBean.setTotalPage(totalPage);

        //5、封装当前页的信息
        //计算当前页第一条数据的索引
        int index = (currentPage - 1) * currentCount;
        String sql2 = "SELECT student.stu_id,student.`name`,student.email,student.birthdate,student.age,student.sex,student.phone FROM student WHERE 1=1";
        if (name != null && !name.equals("")) {
            sql2 += " AND `name` like ?";
        }
        if (sex != null && !sex.equals("")) {
            sql2 += " AND sex=?";
        }
        sql2 += " LIMIT ?,?";
        // System.out.println("sql2" + sql2);
        params.add(index);
        params.add(currentCount);

        pageBean.setList(runner.query(sql2, new BeanListHandler<Student>(Student.class), params.toArray(new Object[0])));

        return pageBean;
    }

    public void editStudentAndUploadPhoto(Student student) throws SQLException {
        QueryRunner runner = new QueryRunner(HikariUtils.getDataSource());
        String sql = "UPDATE `student` SET `name`=?, `email`=?, `birthdate`=?, `age`=?, `sex`=?, `phone`=?, `photo_byte`=?, `photo_base64`=? WHERE stu_id=?";
        runner.update(sql, student.getName(), student.getEmail(), student.getBirthdate(), student.getAge(), student.getSex(), student.getPhone(), new SerialBlob(student.getPhoto_byte()), student.getPhoto_base64(), student.getStu_id());
    }
}
